package com.srv.persistence.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.srv.Config;
import com.srv.common.DataSourceManager;
import com.srv.common.TravelField;
import com.srv.model.Travel;
import com.srv.persistence.ITravelPersistence;
import com.srv.persistence.PersistenceException;
import com.srv.util.DataBaseUtil;
import com.srv.util.ObjectField;
import com.srv.util.SqlUtil;
import com.srv.util.StringUtil;

public class DBTravelPersistence implements ITravelPersistence {

	private static Log log = LogFactory.getLog(DBTravelPersistence.class);
	
	private static final String KEY_TABLE_NAME_PREFIX = "TRAVEL_";
	
	private static final int TABLE_NUM = Config.TABLE_NUM;
	
	public Travel getTravel(long pid, String travelID)
			throws PersistenceException {
		Map<ObjectField, Object> keyValues = new HashMap<ObjectField, Object>();
		keyValues.put(TravelField.TRAVELID, travelID);
		
		List<Travel> list = queryTravelList(pid, keyValues);
		if(list.size() > 0){
			return list.get(0);
		}
		return null;
	}

	public Travel insertTravel(Travel travel) throws PersistenceException {
		if(travel.pid < 1){
			throw new PersistenceException("the column \"pid\" is must NOT NULL when insert travel");
		}
		if(travel.travelID == null){
			throw new PersistenceException("the column \"travelID\" is must NOT NULL when insert travel");
		}
		Date currentTime = new Date();
		if(travel.createTime == null){
			travel.createTime = currentTime;
		}
		if(travel.updateTime == null){
			travel.updateTime = currentTime;
		}
		
		Connection conn = null;
		PreparedStatement st = null;
		
		try {
			conn = DataSourceManager.get().getDataSource(travel.pid).getConnection();
			st = conn.prepareStatement(getInsertSql(travel.pid));
			
			st.setLong(1, travel.pid);
			st.setString(2, travel.travelID);
			st.setString(3, travel.title);
			st.setInt(4, travel.isClose() ? 1 : 0);
			st.setTimestamp(5, travel.finishTime == null ? null : new Timestamp(travel.finishTime.getTime()));
			st.setLong(6, travel.favorNum);
			st.setLong(7, travel.downloadNum);
			st.setLong(8, travel.rePubNum);
			st.setTimestamp(9, new Timestamp(travel.createTime.getTime()));
			st.setTimestamp(10, new Timestamp(travel.updateTime.getTime()));
			st.setInt(11, travel.isDel() ? 1 : 0);
			int flag = st.executeUpdate();
			if(flag <= 0){
				throw new PersistenceException("insert travel fail");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new PersistenceException(e.getMessage());
		}finally{
			DataBaseUtil.closeStatment(st);
			DataBaseUtil.closeConnection(conn);
		}
		return travel;
	}

	public List<Travel> queryTravelList(long pid, Map<ObjectField, Object> keyValues)
			throws PersistenceException {
		String sql = getSelectSql(pid, keyValues);
		log.debug("The Travel query sql is:" + sql);
		
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		List<Travel> list = new ArrayList<Travel>();
		
		try {
			conn = DataSourceManager.get().getDataSource(pid).getConnection();
			st = conn.prepareStatement(sql);
			SqlUtil.setStmtParams(st, 1, keyValues);
			rs = st.executeQuery();
			
			while(rs.next()){
				list.add(rsToObject(rs));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new PersistenceException(e.getMessage());
		} finally{
			DataBaseUtil.closeResultSet(rs);
			DataBaseUtil.closeStatment(st);
			DataBaseUtil.closeConnection(conn);
		}
		return list;
	}

	public boolean updateTravel(long pid, String travelID, Map<ObjectField, Object> keyValues)
			throws PersistenceException {
		String sql = getUpdateSql(pid, keyValues);
		log.debug("The Travel update sql is:" + sql);
		
		Connection conn = null;
		PreparedStatement st = null;
		
		try {
			conn = DataSourceManager.get().getDataSource(pid).getConnection();
			st = conn.prepareStatement(sql);
			int idx = SqlUtil.setStmtParams(st, 1, keyValues);
			st.setLong(idx, pid);
			st.setString(idx + 1, travelID);
			
			return st.executeUpdate() > 0;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new PersistenceException(e.getMessage());
		} finally{
			DataBaseUtil.closeStatment(st);
			DataBaseUtil.closeConnection(conn);
		}
	}
	
	//TODO
	public void deleteTravel(long pid, String travelID)throws PersistenceException{
		
	}
	private String getUpdateSql(long pid, Map<ObjectField, Object> keyValues){
		StringBuilder strBuf = new StringBuilder();
		strBuf.append("UPDATE ");
		strBuf.append(KEY_TABLE_NAME_PREFIX + StringUtil.appendZore(pid % TABLE_NUM, 2));
		
		if (keyValues != null && keyValues.size() > 0) {
			strBuf.append(" SET ").append(SqlUtil.generateSetCause(keyValues));
		}
		strBuf.append(" WHERE PID = ? AND TRAVEL_ID = ?");
		
		return strBuf.toString();
	}
	
	private String getSelectSql(long pid, Map<ObjectField, Object> keyValues){
		StringBuilder builder = new StringBuilder();
		builder.append("SELECT * FROM ");
		builder.append(KEY_TABLE_NAME_PREFIX + StringUtil.appendZore(pid % TABLE_NUM, 2));
		
		if (keyValues != null && keyValues.size() > 0) {
			builder.append(" WHERE ").append(SqlUtil.generateWhereCause(keyValues));
        }
		
        return builder.toString();
	}
	
	private String getInsertSql(long pid){
		StringBuilder builder = new StringBuilder();
		
		builder.append("INSERT INTO ").append(KEY_TABLE_NAME_PREFIX);
		builder.append(StringUtil.appendZore(pid % TABLE_NUM, 2));
		builder.append("(PID, TRAVEL_ID, TITLE, STATUS, FINISH_TIME, FAVOR_NUM, DOWNLOAD_NUM, REPUB_NUM, CREATE_TIME, UPDATE_TIME, ISDEL)");
		builder.append(" VALUES ");
		builder.append("(?,?,?,?,?,?,?,?,?,?,?)");
		
		return builder.toString();
	}
	
	private Travel rsToObject(ResultSet rs) throws SQLException{
		Travel travel = new Travel();
		
		travel.pid = rs.getLong("PID");
		travel.travelID = rs.getString("TRAVEL_ID");
		travel.title = rs.getString("TITLE");
		travel.setStatus(rs.getInt("STATUS"));
		Timestamp tmpTime = rs.getTimestamp("FINISH_TIME");
		travel.finishTime = (tmpTime == null ? null : new Date(tmpTime.getTime()));
		travel.favorNum = rs.getLong("FAVOR_NUM");
		travel.downloadNum = rs.getLong("DOWNLOAD_NUM");
		travel.rePubNum = rs.getLong("REPUB_NUM");
		travel.createTime = new Date(rs.getTimestamp("CREATE_TIME").getTime());
		travel.setIsDel(rs.getInt("ISDEL"));
		travel.updateTime = new Date(rs.getTimestamp("UPDATE_TIME").getTime());
		
		return travel;
	}

}
